using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using QLVMBAYDomain;
using System.Linq;
namespace QLVMBAYDatacess
{
    public class ChuyenBayDA : DataAccessBase
    {
		#region Constructors
		
		public ChuyenBayDA() : base()
        {
			_selectSql = "Select * From CHUYENBAY";
            _insertSql = "Insert Into CHUYENBAY(MACB,MATB,MAHANG,TENCB,NGAYBAY,TGKHOIHANH,TGBAY,SLHANHKHACH) Values (@MACB,@MATB,@MAHANG,@TENCB,@NGAYBAY,@TGKHOIHANH,@TGBAY,@SLHANHKHACH)";
            _updateSql = "Update CHUYENBAY Set MATB=@MATB,MAHANG=@MAHANG,TENCB=@TENCB,NGAYBAY=@NGAYBAY,TGKHOIHANH=@TGKHOIHANH,TGBAY=@TGBAY,SLHANHKHACH=@SLHANHKHACH Where 1=1 And MACB=@MACB";
        	_deleteSql = "Delete From CHUYENBAY Where 1=1 And MACB=@MACB";
        	_primarSql = "Select * From CHUYENBAY Where 1=1 And MACB=@MACB";
        }
		
		protected static ChuyenBayDA _inst = null;
		public static ChuyenBayDA Inst
		{
			get
			{
				if(_inst == null)
					_inst = new ChuyenBayDA();
				return _inst;
			}
		}
		
		#endregion
		
        #region Get Methods

		private ChuyenBay GetCurrent (IDataReader reader, bool isDeep)
		{
			ChuyenBay ret = new ChuyenBay();
			ret.MaChuyenBay = (string)reader["MACB"];
			ret.TenChuyenBay = (string)reader["TENCB"];
            ret.NgayBay = (DateTime)reader["NGAYBAY"];
            ret.TgKhoiHanh = (System.TimeSpan)reader["TGKHOIHANH"];
			ret.ThoiGianBay = (System.TimeSpan)reader["TGBAY"];
			ret.SLHanhKhach = (int)reader["SLHANHKHACH"];
			
			ret.ItemHangHangKhong = new HangHangKhong();
			if(isDeep)
			{
				ret.ItemHangHangKhong =  HangHangKhongDA.Inst.GetByPrimaryKey(isDeep, (string)reader["MAHANG"]);
			}
			else
				ret.ItemHangHangKhong.MaHang = (string)reader["MAHANG"];
			
			ret.ItemTuyenBay = new TuyenBay();
			if(isDeep)
			{
				ret.ItemTuyenBay =  TuyenBayDA.Inst.GetByPrimaryKey(isDeep, (string)reader["MATB"]);
			}
			else
				ret.ItemTuyenBay.MaTuyenBay = (string)reader["MATB"];
			
			
			return ret;
		}

		public List<ChuyenBay> GetAll(bool isDeep)
		{
			DbCommand comm = GetCommand(_selectSql);
            using (Connection)
            {
                using (DbDataReader dr = comm.ExecuteReader())
                {
                    List<ChuyenBay> ret = new List<ChuyenBay>();
					while(dr.Read())
					{
						ret.Add(GetCurrent(dr, isDeep));
					}
					return ret;
                }
            }
		}
		
		public ChuyenBay GetByPrimaryKey(bool isDeep, params object[] args)
		{
			DbCommand cmd = GetCommand(_primarSql);
			if(args.Length < 1)
			{
				throw new Exception();
			}
            SetParam(cmd, "@MACB", args[0]);

            using (Connection)
            {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
					if(dr.Read())
					{
						return GetCurrent(dr, isDeep);
					}
					return null;
                }
            }
		}
		
		public bool Insert(ChuyenBay entity)
		{
			DbCommand cmd = GetCommand(_insertSql);
            SetParam(cmd, "@MACB", entity.MaChuyenBay);
            SetParam(cmd, "@TENCB", entity.TenChuyenBay);
            SetParam(cmd, "@NGAYBAY", entity.NgayBay);
            SetParam(cmd, "@TGKHOIHANH", entity.TgKhoiHanh);
            SetParam(cmd, "@TGBAY", entity.ThoiGianBay);
            SetParam(cmd, "@SLHANHKHACH", entity.SLHanhKhach);

			SetParam(cmd, "@MAHANG", entity.ItemHangHangKhong.MaHang);
			SetParam(cmd, "@MATB", entity.ItemTuyenBay.MaTuyenBay);
			
            using (Connection)
            {
				int ret = cmd.ExecuteNonQuery();
				return ret > 0;
            }
		}
		
		public bool Update(ChuyenBay entity)
		{
			DbCommand cmd = GetCommand(_updateSql);
            SetParam(cmd, "@MACB", entity.MaChuyenBay);
            SetParam(cmd, "@TENCB", entity.TenChuyenBay);
            SetParam(cmd, "@NGAYBAY", entity.NgayBay);
            SetParam(cmd, "@TGKHOIHANH", entity.TgKhoiHanh);
            SetParam(cmd, "@TGBAY", entity.ThoiGianBay);
            SetParam(cmd, "@SLHANHKHACH", entity.SLHanhKhach);

			SetParam(cmd, "@MAHANG", entity.ItemHangHangKhong.MaHang);
			SetParam(cmd, "@MATB", entity.ItemTuyenBay.MaTuyenBay);
			

            using (Connection)
            {
				int ret = cmd.ExecuteNonQuery();
				return ret > 0;
            }
		}
		
		public bool Delete(ChuyenBay entity)
		{
			DbCommand cmd = GetCommand(_deleteSql);
            SetParam(cmd, "@MACB", entity.MaChuyenBay);

            using (Connection)
            {
				int ret = cmd.ExecuteNonQuery();
				return ret > 0;
            }
		}

        public List<ChuyenBay> GetListChuyenBay(string MaTPDi, string MaTPDen, DateTime dtTimeDi, DateTime dtTimeDen, bool IsKhuHoi,bool isDeep)
        {
            var getCBDi = GetListCBay(MaTPDi, MaTPDen, dtTimeDi, isDeep,0);
            if (IsKhuHoi)
            {
                var getCBDen = GetListCBay(MaTPDen, MaTPDi, dtTimeDen, isDeep, 1);
                if (getCBDen.Count == 0)
                {
                    return null; 
                }
                getCBDi = getCBDi.Concat(getCBDen).ToList();
            }
            return getCBDi;
 
        }
        private List<ChuyenBay> GetListCBay(string MaTPDi, string MaTPDen, DateTime dTimeDi, bool isDeep,int ChieuBay)
        {
            var strSQL = "select distinct cb.* from CHUYENBAY cb join TUYENBAY tb on cb.MATB=tb.MATB"
            + " where tb.MASBDI=(select top 1 sb.MASB from SANBAY sb where sb.MATP=@MaTPDi)"
            + " and tb.MASBDEN=(select top 1 sb.MASB from SANBAY sb where sb.MATP=@MaTPDen) and cb.NGAYBAY = @NGAYBAY";
            DbCommand comm = GetCommand(strSQL);
            SetParam(comm, "@MaTPDi", MaTPDi);
            SetParam(comm, "@MaTPDen", MaTPDen);
            SetParam(comm, "@NGAYBAY", dTimeDi);
            var ret = GetChuyenBay(comm, ChieuBay, isDeep);
            return ret;
        }
        private List<ChuyenBay> GetChuyenBay(DbCommand comm,int ChieuBay,bool isDeep)
        {
            using (Connection)
            {
                using (DbDataReader dr = comm.ExecuteReader())
                {
                    List<ChuyenBay> ret = new List<ChuyenBay>();
                    while (dr.Read())
                    {
                        var item = GetCurrent(dr, isDeep);
                        item.DsChiTietCB = ChiTietCBayDA.Inst.GetListTTVeByMaCB(item.MaChuyenBay);
                        item.ChieuBay = ChieuBay;
                        ret.Add(item);
                    }
                    return ret;
                }
            }
        }
        public List<ChuyenBay> GetListCBayByMaTenSB(string MaSB,bool ChieuDi,bool isDeep)
        {
            var strSQL = "select distinct cb.* from CHUYENBAY cb join TUYENBAY tb on cb.MATB=tb.MATB";
            if (ChieuDi)
            {
                strSQL += " where tb.MASBDI=@MASB";
            }
            else
            {
                strSQL += " where tb.MASBDEN=@MASB";
            }

            DbCommand comm = GetCommand(strSQL);
            SetParam(comm, "@MASB", MaSB);

            var ret = GetChuyenBay(comm, ChieuDi ? 0 : 1, isDeep);
            return ret;
        }
        public List<ChuyenBay> GetListCBayByDK(DIEU_KIEN_TIM DKTim,float Gia, string MaLoaiGhe, DateTime dTimeNgayDi,bool isDeep)
        {
            var strSQL = "select distinct cb.* from CHUYENBAY cb join CHITIETCBAY ct on cb.MACB=ct.MACB "
            +"join  VE_GHE vghe on ct.MAGHEVE=vghe.MAGHEVE where ";
            string DKWhere = "";
            if (dTimeNgayDi != DateTime.MinValue)
            {
                DKWhere = " cb.NGAYBAY=@NgayBay";
            }
            if (Gia>0)
            {
                if (!string.IsNullOrEmpty(DKWhere))
                {
                    DKWhere += " and ";
                }
                string BThuc = DKTim == DIEU_KIEN_TIM.GIA_LON_HON_BANG ? ">=" : "<=";
                DKWhere += " tct.GIAVE" + BThuc + "@GiaVe";
            }
            if (!string.IsNullOrEmpty(MaLoaiGhe))
            {
                if (!string.IsNullOrEmpty(DKWhere))
                {
                    DKWhere += " and ";
                }
                DKWhere += "vghe.MALOAIGHE=@MaLoaiGhe";
            }            

            DbCommand comm = GetCommand(strSQL+DKWhere);
            if (dTimeNgayDi != DateTime.MinValue)
            {
                SetParam(comm, "@NgayBay", dTimeNgayDi);
            }
            SetParam(comm, "@GiaVe", Gia);
            SetParam(comm, "@MaLoaiGhe", MaLoaiGhe);
            var ret = GetChuyenBay(comm, 0, isDeep);
            return ret;
        }
		#endregion
    }
}


